package cn.keepme.common.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.NumberFormat;
import java.util.Date;

import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;

/**
 * @author sjz
 */
public class ExcelUtils {
	
	public static String[][] readExcel(File file,int sheetNum,int startRowNum,int startColNum) throws IOException{
		FileInputStream is = new FileInputStream(file);
		String[][] string=readExcel(is,sheetNum,startRowNum,startColNum);
		return string;
	}
	
	public static String[][] readExcel(InputStream is,int sheetNum,int startRowNum,int startColNum) throws IOException{
		HSSFWorkbook wb = new HSSFWorkbook(is);
		HSSFSheet childSheet = wb.getSheetAt(sheetNum);
		int rows=childSheet.getLastRowNum();
		int cols=childSheet.getRow(startRowNum).getLastCellNum();
		int tempRows=rows+1-startRowNum;
		int tempCols=cols-startColNum;
		
		String[][] string=new String[tempRows][tempCols];
		int tempRowNum=startRowNum;
		for(int i=0;i<tempRows;i++){
			int tempColNum=startColNum;
			for(int j=0;j<tempCols;j++){
				String str="";
				HSSFCell cell=childSheet.getRow(tempRowNum).getCell(tempColNum);
				if(cell!=null){
					int cellType=cell.getCellType();
					if(cellType==Cell.CELL_TYPE_STRING){
						str=childSheet.getRow(tempRowNum).getCell(tempColNum).getStringCellValue();
					}else if(cellType==Cell.CELL_TYPE_NUMERIC){
						if(HSSFDateUtil.isCellDateFormatted(cell)){
							Date date=cell.getDateCellValue();
							str=DateFormatUtils.format(date, "yyyy-MM-dd HH:mm:ss");
						}else{
							//str=String.valueOf((double)childSheet.getRow(tempRowNum).getCell(tempColNum).getNumericCellValue());
							double v=(double)childSheet.getRow(tempRowNum).getCell(tempColNum).getNumericCellValue();
							NumberFormat formater=NumberFormat.getInstance();
							formater.setGroupingUsed(false);
							formater.setMaximumFractionDigits(5);
							str=formater.format(v);
						}
					}
				}
				string[i][j]=str;
				tempColNum++;
			}
			tempRowNum++;
		}
		return string;
	}
	
	public static void main(String[] args){
		try{
			File file=new File("f://abc.xls");
			String[][] string=readExcel(file,0,0,0);
			for(int i=0;i<string.length;i++){
				System.out.println(string[i][0]+"/"+string[i][1]);
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}
	}
}
